﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SD.Common.Utilities;
using System.Data;

namespace SD.DAL
{
    public class BaseDAO
    {
        public const string OrgDetails2Spec = "Organization Specicalism";
        public const string OrgDetails2SerDisCap = "Service Disabilities Capabilities";
        public const string OrgDetails2SerBarCap = "Service Barriers Capabilities";
        public const string OrgDetails2SerBenCap = "Service Benefits Capabilities";
        public const string OrgDetails2SerPerCirCap = "Service Personal Circumstances Capabilities";
        public const string OrgDetails2SerEthCap = "Service Ethnicity Capabilities";
        public const string OrgDetails2Accre = "Accreditation";


        /// <summary>
        /// Creates and prepares a new SqlCommand object on a new connection
        /// </summary>
        /// <returns></returns>
        public SqlCommand CreateCommand()
        {
            // Obtain SqlConnection object
            SqlConnection conn = Connector.GetConnection();

            // Create a database-specific command object
            SqlCommand comm = conn.CreateCommand();

            // Set the command type to stored procedure
            comm.CommandType = CommandType.StoredProcedure;

            // Return the initilized commnand object
            return comm;
        }

        /// <summary>
        /// Executes a SELECT command and returns the results as a DataTable object
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public DataTable ExecuteSelectCommand(SqlCommand command)
        { 
            // The DataTable to be returned
            DataTable table;

            // Execute the command making sure the connection gets closed in the end
            try
            {
                // Open the connection of the command
                command.Connection.Open();

                // Execute the command and save the results in a DataTable
                SqlDataReader reader = command.ExecuteReader();
                table = new DataTable();
                table.Load(reader);

                // Close the reader
                reader.Close();
            }
            catch (Exception ex )
            {                
                throw ex;
            }
            finally
            {
                // Close the connection
                command.Connection.Close();
            }

            return table;
        }

        /// <summary>
        /// Executes an UPDATE, DELETE, or INSERT command
        /// and return the number of affected rows
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(SqlCommand command)
        { 
            // The number of affected rows
            int affectedRows = -1;

            // Execute the command making sure the connection gets closed in the end
            try
            {
                // Open the connection of the command
                command.Connection.Open();

                // Execute the command and get the number of affected rows
                affectedRows = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Close the connection
                command.Connection.Close();
            }

            // return the number of affected rows
            return affectedRows;
        }

        /// <summary>
        /// Executes a SELECT command and return a single result as a string
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public string ExecuteScalar(SqlCommand command)
        { 
            // The value to be returned
            string value = "";

            // Execute the command making sure the connection gets closed in the end
            try
            {
                // Open the connection of the command
                command.Connection.Open();

                // Execute the command and get the result
                value = command.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Close the connection
                command.Connection.Close();
            }

            // return the result
            return value;
        }
    }
}
